#! /usr/bin/env python
# _*_ coding:UTF-8 _*_
"""
@author:zhh
@Software:PyCharm
@blogs:https://blog.csdn.net/smilehaoh
@time:2020/3/31

"""
from common.connect_db import MyDB

my_db = MyDB()


class Credits:

    @staticmethod
    def get_old_total_credits():
        """
        查询老系统会员的总积分
        :return:
        """
        sql1 = "SELECT sum(totalcredit1) as oldtotalcredit from ims_ewei_shop_member where id in (select id from ims_ewei_shop_member where (mobile regexp '[^0-9.]')=0)"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_total_credits1():
        """
        查询老系统会员的总积分
        :return:
        """
        sql1 = "SELECT totalcredit1 as oldtotalcredit from ims_ewei_shop_member"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_credits(normal_level):
        """
        查询老系统其他会员的总积分
        :return:
        """
        sql = "SELECT sum(totalcredit1) as total from ims_ewei_shop_member where level=%s and id in (select id from ims_ewei_shop_member where (mobile regexp '[^0-9.]')=0)" % normal_level
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_credits(level_number):
        """
        查询新系统普通会员总积分
        :return:
        """
        sql = "select sum(now_credits) as total from common_coin_credits where account_type=1 and level='%s'" % level_number
        result = my_db.execute_select(select_db='new', db_name="coin", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_credits2():
        """
        所有vip会员积分总数
        :return:
        """
        sql = "select sum(now_credits) as total from common_coin_credits where level in (20,30,40)"
        result = my_db.execute_select(select_db='new', db_name="coin", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_credits1(level_number):
        """
        查询新系统普通会员总积分
        :return:
        """
        sql = "select now_credits as total from common_coin_credits where account_type=1 and level='%s'" % level_number
        result = my_db.execute_select(select_db='new', db_name="coin", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_total_credits():
        """
        查询新系统会员的总积分
        :return:
        """
        sql = "select sum(now_credits) as number_now_credits  from common_coin_credits where account_type=1 "
        result = my_db.execute_select(select_db='new', db_name="coin", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_total_credits1():
        """
        查询新系统会员的总积分
        :return:
        """
        sql = "select now_credits as number_now_credits  from common_coin_credits where account_type=1 "
        result = my_db.execute_select(select_db='new', db_name="coin", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_new_credits0():
        """
        查询新系统会员积分为0的数量
        :return:
        """
        sql = "select count(*) as number_now_credits  from common_coin_credits where account_type=1 and now_credits=0"
        result = my_db.execute_select(select_db='new', db_name="coin", sql_text=sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")

    @staticmethod
    def get_old_total0():
        """
        查询老系统会员积分为0的数量
        :return:
        """
        sql1 = "SELECT count(totalcredit1) as oldtotalcredit from ims_ewei_shop_member where totalcredit1=0 and id in (select id from ims_ewei_shop_member where (mobile regexp '[^0-9.]')=0)"
        result = my_db.execute_select(select_db='old', db_name="quanfan", sql_text=sql1)
        if len(result) > 0:
            return result
        else:
            raise ValueError("没有查询到相关数据")


if __name__ == '__main__':
    credit = Credits()
    a = Credits.get_new_credits0()
